package com.sq.partner.manager.dao.impl;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import com.sq.partner.manager.dao.IGatewayTradPlanCovDao;
import com.sq.partner.manager.mybatis.model.TradPlanCov;
import com.sq.partner.manager.mybatis.model.TradProdInst;
import com.sq.partner.manager.util.DBUtil;
import com.sq.partner.manager.util.LogUtil;
import com.sq.partner.manager.util.Page;
import com.sq.partner.manager.util.SqlUtil;
@Repository
public class GatewayTradPlanCovDaoImpl  extends BaseDaoImpl implements IGatewayTradPlanCovDao {
	private static Logger logger = LogUtil.getLog(GatewayTradPlanCovDaoImpl.class);

	@Override
	public TradPlanCov queryTradPlanCov(TradPlanCov tradPlanCov) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void saveTradPlanCovList(List<TradPlanCov> tradplancovList) throws Exception {
		if(null!=tradplancovList&&tradplancovList.size()>1){
			this.add("insertTradPlanCovList", tradplancovList);
		}else{
			this.add("insertTradPlanCov", tradplancovList.get(0));
		}
		
	}

	@Override
	public Page<TradPlanCov> queryCoveragePage(Page<TradPlanCov> page) throws SQLException {
		Connection conn = null;
		//查总条数
		PreparedStatement pstmt = null;
		//查分页
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		try{
			conn = this.getConnection();
			StringBuffer sqlbuf=new StringBuffer();
			/*		sqlbuf.append("select tradplanco.TRAD_COV_CODE as tradCovCode, tradcov.TRAD_COV_NAME as tradCovName, tradplanco.COV_AMT as covAmt "
							+ "from T_TRAD_PLAN_COV tradplanco,T_TRAD_COV tradcov  "
							+ "where 1=1 and tradplanco.TRAD_PLAN_CODE=? and tradplanco.TRAD_COV_CODE=tradcov.TRAD_COV_CODE");
			*/
					sqlbuf.append("select  tc.noCalimAmount,tc.claimRate,  tc.tradCovCode as tradCovCode,cc.covnme as covNme,tc.tradCovName as tradCovName,tc.covamt as covAmt,cc.corecovcode as corecovcode,tc.isInputAmount as isInputAmount,cc.corecovcode as covCode from "
							+ "(select tradplanco.TRAD_COV_CODE as tradCovCode,"
							+ "tradcov.TRAD_COV_NAME    as tradCovName,"
							+ "tradplanco.COV_AMT       as covAmt,"
							+ "tradcov.cov_code as covCode,"
							+ "tradplanco.no_calim_amount as noCalimAmount,"
							+ "tradplanco.claim_rate*100 as claimRate,"
							+ "tradplanco.is_input_amount as isInputAmount "
							+ "from T_TRAD_PLAN_COV tradplanco, T_TRAD_COV tradcov "
							+ " where 1 = 1 and tradplanco.TRAD_COV_CODE = tradcov.TRAD_COV_CODE "
							+ "   and tradplanco.trad_plan_code = ? ) tc,("
							+ "select cv.covcode as corecovcode,cv.covnme,pc.plan_code from  t_core_coverage  cv,t_core_plan_cov  pc"
							+ " where cv.covcode = pc.covcode and pc.plan_code = ? and pc.inst_code = ?) cc "
							+ " where tc.covCode(+) = cc.corecovcode order by cc.corecovcode desc");
					
					String sql="";
					
					TradPlanCov tradPlanCov=page.getType();
					
					
					sql=sqlbuf.toString();
					logger.debug("执行SQL：" + sql);


					pstmt = conn.prepareStatement("select count(*) from ("+sql+")");

					pstmt.setObject(1, tradPlanCov.getTradPlanCode());
					pstmt.setObject(2, tradPlanCov.getTradCovCode());
					pstmt.setObject(3, tradPlanCov.getInstCode());
					rs = pstmt.executeQuery();
					rs.next();
					Long total = rs.getLong(1);
					
					//查出分页
					page.setTotal(total);
					SqlUtil sqlUtil=new	SqlUtil();
					
					
					String pagesql=sqlUtil.buildPageSqlForOracle(sql,page).toString();
					logger.debug("执行SQL："+pagesql);
					pstmt1 = conn.prepareStatement(pagesql);
					
					
					pstmt1.setObject(1,  tradPlanCov.getTradPlanCode());
					pstmt1.setObject(2, tradPlanCov.getTradCovCode());
					pstmt1.setObject(3, tradPlanCov.getInstCode());
					
					rs = pstmt1.executeQuery();
					List lists = DBUtil.transRs2List(rs);
					page.setData(lists);
		}finally{
			close(rs);
			close(pstmt);
			close(pstmt1);
			close(conn);
		}
		return page;
	}

	@Override
	public void updateTradPlanCov(TradPlanCov tradPlanCov) throws Exception {
		this.update("updateTradPlanCov", tradPlanCov);
	}

	@Override
	public List<TradPlanCov> queryTradPlanCovList(TradPlanCov tradPlanCov)
			throws SQLException {
		return this.queryList("queryTradPlanCovByMap", tradPlanCov);
	}

	@Override
	public void deleteTradPlanCov(TradPlanCov tradPlanCovs) throws Exception {
		this.delete("delTradPlanCov", tradPlanCovs);
		
	}

	@Override
	public List<TradPlanCov> findState(TradProdInst tradProdInst)
			throws SQLException {
		// TODO Auto-generated method stub
		return this.queryList("queryTradPlanCovByProdCode", tradProdInst);
	}
}
